This script will present several new components of the Nonprofit Open Data Collective data environment using an example motivated by the “Glass Cliff”, or the idea the women are more likely to be appointed to precarious positions of power relative to their male counterparts.
We specifically want to highlight the value of an integrated data environment developed around the EFILE Database, one of the emerging research centerpieces in nonprofit scholarship. This tutorial demonstrates a reproducible data engineering workflow that follows FAIR Data Guidelines. We will answer the research question using a dataset created through the following 8 Steps:
We are using the term “integrated data environment” here to mean:
These things allow us to create more expressive and intuitive data steps that anyone can replicate. It also allows researchers to create custom tools for refining or analyzing data, as demonstrated by the packages used in this workflow.
The glass cliff is a hypothesized phenomenon in which women are more likely to break the “glass ceiling” (i.e. achieve leadership roles in business and government) during periods of crisis or downturn when the risk of failure is highest.
We know from previous studies that male CEOs are put in charge of large, thriving nonprofits while women are more likely to be hired to lead smaller social services organizations.
Here we explore the question of whether female CEOs are more likely to be hired when the current leader is struggling.
Grasse, N. J., Heidbreder, B., Kukla-Acevedo, S. A., & Lecy, J. D. (2024). Some Good News, More Bad News: Two Decades of the Gender Pay Gap for Nonprofit Directors and Chief Financial Officers. Review of Public Personnel Administration, 0734371X241248854.
Processed IRS 990 Efile data are housed in the data catalog with the respective data dictionaries. You can find further information about sources, processing, and final parsing on these sites .
We will be using the following NODC packages for the demo:
devtools::install_github( 'Nonprofit-Open-Data-Collective/peopleparser' )
devtools::install_github( 'nonprofit-open-data-collective/titleclassifier' )
devtools::install_github( 'nonprofit-open-data-collective/fiscal')library( tidyverse )
library( pander )
# nonprofit data packages
library( peopleparser )
library( titleclassifier )
library( fiscal )
# helper functions for the demo:
nodc <- "https://raw.githubusercontent.com/Nonprofit-Open-Data-Collective/"
repo <- "arnova-2024/refs/heads/main/"
file <- "functions.R"
source( paste0( nodc, repo, file ) )This step builds Efile tables by loading raw XML efile returns from the Data Commons and using the irs990efile package to parse XML files into rectangular CSV tables.
# Create sample of 100
# organizations from 2018
# library( irs990efile )
index <- build_index( tax.years=2018 )
index100 <-
index %>%
filter( FormType %in% c("990","990EZ") ) %>%
sample_n( 100 )
TABLES <- c( "F9-P00-T00-HEADER",
"F9-P01-T00-SUMMARY",
"F9-P08-T00-REVENUE",
"F9-P09-T00-EXPENSES",
"F9-P11-T00-ASSETS" )
URLS <- index100$URL
build_tables( urls=URLS, year=2018, table.names=TABLES )Typically you would not need to replicate this step since it is a computationally-intensive process. It can take a couple of days to build the full efile database. It is much easier to pull existing CSV files from NCCS: EFILE DATA CATALOG.
root <- paste0( nodc, repo )
fn <- "data/PART-VII-SAMPLE-10.CSV"
url <- paste0( root, fn )
partvii <- read.csv( url )| NAME | TAXYR | FORMTYPE | F9_07_COMP_DTK_NAME_PERS |
|---|---|---|---|
| President and Fellows of Middlebury College | 2009 | 990 | Ronald Liebowitz |
| President and Fellows of Middlebury College | 2009 | 990 | Frederick M Fritz |
| President and Fellows of Middlebury College | 2009 | 990 | Marna C Whittington |
| President and Fellows of Middlebury College | 2009 | 990 | Kendrick R Wilson III |
| President and Fellows of Middlebury College | 2009 | 990 | S Carolyn Ramos |
| President and Fellows of Middlebury College | 2009 | 990 | Roxanne M Leighton |
| F9_07_COMP_DTK_TITLE | F9_07_COMP_DTK_AVE_HOUR_WEEK | F9_07_COMP_DTK_COMP_ORG |
|---|---|---|
| President | 40 | 409609 |
| Chair | 5 | 0 |
| Vice Chair | 5 | 0 |
| Vice Chair | 5 | 0 |
| Alumni Trustee | 1 | 0 |
| Charter Trustee | 1 | 0 |
Now let’s use peopleparser to clean the names. Names do not arrive standardized in any specific format:
## Karen A Jackson ;; FERDINAND JONES ;; McCarron Edward
## BRUCE STEDMAN ;; BETH PANELLA ;; SUSAN MCNAMARA
## NICHOLAS LASOFF ;; Jon Lumbra ;; A HERBERT SCHWARTZ MD
## JOSEPH BATOR ;; RITA WARNOCK ;; PAUL WASSON
## STELLA CHAN ;; John McCardell ;; ELLEN KINLIN
The peopleparser package will remove nuisance text and split the name string into 5 parts:
And add a predicted gender label (M/F/U) plus confidence level once it is able to identify the individual’s first name:
## [1] "REV|KENDRICK|R|WILSON|III||M|100"
## [1] "DOCTOR|KENDRICK|R|WILSON|III||M|100"
# gender prediction is based on first names
peopleparser::parse.name( "Wilson, K R Until June 2008" )## [1] "|K|R|WILSON|||U|0.0"
nm <- partvii[[ "F9_07_COMP_DTK_NAME_PERS" ]] %>% unique()
nm.parsed <- peopleparser::parse.names( nm )| name | salutation | first_name | middle_name | last_name |
|---|---|---|---|---|
| Ronald Liebowitz | RONALD | LIEBOWITZ | ||
| Frederick M Fritz | FREDERICK | M | FRITZ | |
| Marna C Whittington | MARNA | C | WHITTINGTON | |
| Kendrick R Wilson III | KENDRICK | R | WILSON | |
| S Carolyn Ramos | S | CAROLYN | RAMOS | |
| Roxanne M Leighton | ROXANNE | M | LEIGHTON |
| suffix | status | gender | gender_confidence |
|---|---|---|---|
| M | 99.7 | ||
| M | 100 | ||
| F | 100 | ||
| III | M | 100 | |
| F | 100 | ||
| F | 100 |
Join parsed names back to the original data frame.
# CORE R VERSION OF A TABLE JOIN
partvii <-
partvii %>%
merge( nm.parsed,
by.x="F9_07_COMP_DTK_NAME_PERS", by.y="name",
all.x=T )root <- paste0( nodc, repo )
fn <- "data/PART-VII-SAMPLE-10-PARSED-NAMES.CSV"
url <- paste0( root, fn )
partvii <- read.csv( url )# steps from titleclassifier package
titles <-
partvii %>%
standardize_df() %>%
remove_dates() %>%
standardize_conj() %>%
split_titles() %>%
standardize_spelling() %>%
gen_status_codes() %>%
standardize_titles() %>%
categorize_titles()## ✔ standardize df step complete
## ✔ remove dates step complete
## ✔ standardize conjunctions step complete
## ✔ split titles step complete
## ✔ standardize spelling step complete
## ✔ generate status codes step complete
## ✔ standardize titles step complete
## ✔ categorize titles step complete
| dtk.name | title.raw | title.mult.x | title.order |
|---|---|---|---|
| KATHRYN DUPREE | EXECUTIVE DIRECTOR | 0 | 1 |
| LINDA GRIMM | ACADEMY DIRECTOR | 0 | 1 |
| ARLENE KAYE | DIRECTOR OF CHILDREN’S BEHAVIORAL SERVICES | 0 | 1 |
| TACIE LOWE | DIRECTOR OF INDIVIDUAL & FAMILY SUPPORT | 0 | 1 |
| CARL J CASPER | CHAIR | 0 | 1 |
| THOMAS IGOE | TREASURER/SECRETARY | 1 | 2 |
| THOMAS IGOE | TREASURER/SECRETARY | 1 | 1 |
| LARRY WOOD | DIRECTOR | 0 | 1 |
| STEPHEN SIMONSON | RESIDENTIAL DIRECTOR | 0 | 1 |
| ARLENE KAYE | CBD DIRECTOR | 0 | 1 |
Title processing steps from the package:
| title.raw | title.v4 | |
|---|---|---|
| 150 | EXECUTIVE DIRECTOR/FSE DIRECTOR | EXECUTIVE DIRECTOR |
| 151 | EXECUTIVE DIRECTOR/FSE DIRECTOR | FSE DIRECTOR |
| 152 | DIRECTOR | DIRECTOR |
| 153 | DIRECTOR | DIRECTOR |
| 154 | DIRECTOR | DIRECTOR |
| 155 | DIRECTOR, FLUTIE FDN. AND FSE | DIRECTOR AND FLUTIE FDN AND FSE |
| 156 | CO-CHAIR/DIRECTOR | CO-CHAIR |
| 157 | DIRECTOR | DIRECTOR |
| 158 | CO-CHAIR/DIRECTOR | DIRECTOR |
| 159 | DIRECTOR | DIRECTOR |
| 160 | DIRECTOR | DIRECTOR |
| 161 | DIRECTOR | DIRECTOR |
| 162 | EXECUTIVE DIRECTOR | EXECUTIVE DIRECTOR |
| 163 | CLERK/DIRECTOR | CLERK |
| 164 | CLERK/DIRECTOR | DIRECTOR |
| 165 | DIRECTOR | DIRECTOR |
| title.v7 | title.standard | |
|---|---|---|
| 150 | CEO | CEO |
| 151 | FSE DIRECTOR | NA |
| 152 | DIRECTOR | DIRECTOR |
| 153 | DIRECTOR | DIRECTOR |
| 154 | DIRECTOR | DIRECTOR |
| 155 | DIRECTOR AND FLUTIE FDN AND FSE | NA |
| 156 | CO-CHAIR | BOARD PRESIDENT |
| 157 | DIRECTOR | DIRECTOR |
| 158 | DIRECTOR | DIRECTOR |
| 159 | DIRECTOR | DIRECTOR |
| 160 | DIRECTOR | DIRECTOR |
| 161 | DIRECTOR | DIRECTOR |
| 162 | CEO | CEO |
| 163 | CLERK | CLERK |
| 164 | DIRECTOR | DIRECTOR |
| 165 | DIRECTOR | DIRECTOR |
Pay and hours tabulated relative to other employees in the same org:
| dtk.name | title.standard | tot.hours | hours.rank | tot.comp | pay.max |
|---|---|---|---|---|---|
| KATHRYN DUPREE | CEO | 37.5 | 1 | 188130 | 188130 |
| LINDA GRIMM | NA | 37.5 | 1 | 179286 | 188130 |
| ARLENE KAYE | NA | 37.5 | 1 | 135385 | 188130 |
| TACIE LOWE | NA | 37.5 | 1 | 118760 | 188130 |
| CARL J CASPER | BOARD PRESIDENT | 0.5 | 2 | 0 | 188130 |
| THOMAS IGOE | BOARD SECRETARY | 0.5 | 2 | 0 | 188130 |
| THOMAS IGOE | BOARD TREASURER | 0.5 | 2 | 0 | 188130 |
| LARRY WOOD | DIRECTOR | 0.5 | 2 | 0 | 188130 |
| pay.rank |
|---|
| 1 |
| 2 |
| 14 |
| 20 |
| 25 |
| 25 |
| 25 |
| 25 |
| dtk.name | title.standard | tot.hours | |
|---|---|---|---|
| 9 | STEPHEN SIMONSON | NA | 37.5 |
| 10 | ARLENE KAYE | NA | 37.5 |
| 11 | TONIA HOLLAND | CHIEF FINANCIAL OFFICER | 37.5 |
| 12 | ALEXANDER WESTPHAL MD | DIRECTOR | 0.5 |
| 13 | KEVIN DALY | DIRECTOR | 0.5 |
| 14 | WICK CHAMBERS | DIRECTOR | 0.5 |
| hours.rank | tot.comp | pay.max | pay.rank | |
|---|---|---|---|---|
| 9 | 1 | 133080 | 188130 | 15 |
| 10 | 1 | 121476 | 188130 | 17 |
| 11 | 1 | 111146 | 188130 | 22 |
| 12 | 2 | 0 | 188130 | 25 |
| 13 | 2 | 0 | 188130 | 25 |
| 14 | 2 | 0 | 188130 | 25 |
| dtk.name | title.standard | tot.hours | |
|---|---|---|---|
| 15 | KATHRYN DUPEE | CEO | 37.5 |
| 16 | JOHN BALDINO | DIRECTOR OF FACILITIES | 37.5 |
| 17 | JOSEPH SPITERI | GENERAL MANAGER | 37.5 |
| 18 | TACIE LOWE | NA | 37.5 |
| 19 | THOMAS IGOE | BOARD TREASURER | 0.5 |
| 20 | KARA FARACLAS | BOARD VICE PRESIDENT | 0.5 |
| 21 | JAMES MCPARTLAND PHD | DIRECTOR | 0.5 |
| 22 | JANETTE JOHNSON | DIRECTOR | 0.5 |
| 23 | STEPHEN WIZNER | DIRECTOR | 0.5 |
| hours.rank | tot.comp | pay.max | pay.rank | |
|---|---|---|---|---|
| 15 | 1 | 176077 | 188130 | 3 |
| 16 | 1 | 141983 | 188130 | 7 |
| 17 | 1 | 141538 | 188130 | 8 |
| 18 | 1 | 120327 | 188130 | 18 |
| 19 | 2 | 0 | 188130 | 25 |
| 20 | 2 | 0 | 188130 | 25 |
| 21 | 2 | 0 | 188130 | 25 |
| 22 | 2 | 0 | 188130 | 25 |
| 23 | 2 | 0 | 188130 | 25 |
We are omitting this step to avoid an overly complex example.
We will first build a financials table by selecting the relevant 990 parts, then combining them:
## JOINING ONE TO ONE TABLES
root <- paste0( nodc, repo )
fn1 <- "data/F9-P00-T00-HEADER-SAMPLE-10.CSV"
fn2 <- "data/F9-P01-T00-SUMMARY-SAMPLE-10.CSV"
fn3 <- "data/F9-P08-T00-REVENUE-SAMPLE-10.CSV"
fn4 <- "data/F9-P09-T00-EXPENSES-SAMPLE-10.CSV"
fn5 <- "data/F9-P10-T00-BALANCE-SHEET-SAMPLE-10.CSV"
d1 <- read.csv( paste0( root, fn1 ) )
d2 <- read.csv( paste0( root, fn2 ) )
d3 <- read.csv( paste0( root, fn3 ) )
d4 <- read.csv( paste0( root, fn4 ) )
d5 <- read.csv( paste0( root, fn5 ) )The efile one-to-one tables all share the same IDs:
## [1] "OBJECTID" "URL" "RETURN_VERSION" "ORG_EIN"
## [5] "ORG_NAME_L1" "ORG_NAME_L2" "RETURN_TYPE" "TAX_YEAR"
## [9] "EIN2"
Which makes merging files easy:
Note that dataset dimensions should not change for one-to-one merges:
## [1] 107 55
## [1] 107 50
## [1] 107 376
See the appendix for the code used to compile the five tables above for the sample.
| F9_01_EXP_TOT_PY | F9_01_EXP_REV_LESS_EXP_CY | F9_01_EXP_REV_LESS_EXP_PY |
|---|---|---|
| 3217608 | 56922 | 31606 |
| 870793 | -194147 | -257955 |
| 692598 | 42635 | 31852 |
| 11765651 | 519017 | 403144 |
| 8708339 | -558773 | -308774 |
The fiscal package contains the following financial ratios:
df <- get_aer( df ) # Assets to Revenues Ratio
df <- get_arr( df ) # Assets to Revenues Ratio
df <- get_cr( df ) # Current Ratio
df <- get_dar( df ) # Debt to Asset Ratio
df <- get_der( df ) # Debt to Equity Ratio
df <- get_dgdr( df ) # Donation/Grant Dependence Ratio
df <- get_dmr( df ) # Debt Management Ratio
df <- get_doch( df ) # Days of Operating Cash on Hand
df <- get_doci( df ) # Days of Operating Cash and Investments
df <- get_eidr( df ) # Earned Income Dependence Ratio
df <- get_er( df ) # Equity Ratio
df <- get_ggr( df ) # Government Grants Ratio
df <- get_iidr( df ) # Investment Income Dependence Ratio
df <- get_lar( df ) # Lands to Assets Ratio
df <- get_moch( df ) # Months of Operating Cash on Hand
df <- get_or( df ) # Operating Margin
df <- get_per( df ) # Program Efficiency Ratio
df <- get_podpm( df ) # Post-Depreciation Profitability Margin
df <- get_predpm( df ) # Pre-Depreciation Profitability Margin
df <- get_qr( df ) # Quick Ratio
df <- get_ssr( df ) # Self Sufficiency Ratio
df <- get_stdr( df ) # Short Term Debt RatioWe will add the post-depreciation profitability margin and the quick ratio to the data:
## [1] "Revenues cannot be equal to zero: 0 cases have been replaced with NA."
## podpm podpm.w podpm.n podpm.p
## Min. :-0.50950 Min. :-0.46216 Min. :-3.3121 Min. : 1
## 1st Qu.:-0.07950 1st Qu.:-0.07950 1st Qu.:-0.4535 1st Qu.:25
## Median : 0.01107 Median : 0.01107 Median : 0.2230 Median :49
## Mean :-0.01858 Mean :-0.01879 Mean : 0.0000 Mean :49
## 3rd Qu.: 0.05557 3rd Qu.: 0.05557 3rd Qu.: 0.5555 3rd Qu.:73
## Max. : 0.38204 Max. : 0.31425 Max. : 2.4878 Max. :97
## NA's :10 NA's :10 NA's :10 NA's :10
## [1] "Net assets cannot be zero: 0 cases have been replaced with NA."
## dgdr dgdr.w dgdr.n dgdr.p
## Min. :0.04165 Min. :0.04638 Min. :-1.6821 Min. : 1
## 1st Qu.:0.42370 1st Qu.:0.42370 1st Qu.:-0.4077 1st Qu.:10
## Median :0.61054 Median :0.61054 Median : 0.2233 Median :19
## Mean :0.54480 Mean :0.54442 Mean : 0.0000 Mean :19
## 3rd Qu.:0.80102 3rd Qu.:0.80102 3rd Qu.: 0.8667 3rd Qu.:28
## Max. :0.92779 Max. :0.90902 Max. : 1.2314 Max. :37
## NA's :70 NA's :70 NA's :70 NA's :70
For details on the definitions and calculation of the ratios try:
The Business Master File contains
important information not available on 990 forms such as organizational
NTEE codes. In addition, the NCCS BMF files contain standardized
geographies and other useful information.
The BMF rows for our sample have been precompiled:
The demo files above (a sample of 10 nonprofits), read directly from the demo repo on GitHub, were compiled using the following code:
EIN2_10 <-
c("EIN-02-0240383", "EIN-03-0179298",
"EIN-04-2104310", "EIN-04-2259692",
"EIN-04-2592472", "EIN-04-2596491",
"EIN-04-3266589", "EIN-04-3543134",
"EIN-05-0258941", "EIN-06-0840436" )
tables <-
c( "F9-P00-T00-HEADER",
"F9-P01-T00-SUMMARY",
"F9-P08-T00-REVENUE",
"F9-P09-T00-EXPENSES",
"F9-P10-T00-BALANCE-SHEET")
for( i in tables )
{
for( j in 2009:2020 )
{
df <- NULL
try( df <- get_table( i, j ) )
if( is.null(df) ){ next }
sub <- dplyr::filter( df$EIN2 %in% EIN2_10 )
fn <- paste0( i, "-", j, "-SAMPLE-10.CSV" )
write.csv( sub, fn, row.names=F, na="" )
}
}
# COMBINE ALL YEARS TO SINGLE FILE
root <- "https://raw.githubusercontent.com/Nonprofit-Open-Data-Collective/arnova-2024/refs/heads/main/data/"
for( i in tables )
{
d.list <- list()
for( j in 2009:2020 )
{
fn <- paste0( i, "-", j, "-SAMPLE-10.CSV" )
url <- paste0( root, fn )
df <- read.csv( url, colClasses = "character" )
d.list[[ as.character(j) ]] <- df
}
dd <- dplyr::bind_rows( d.list )
filename <- paste0( i, "-SAMPLE-10.CSV" )
write.csv( dd, filename, row.names=F, na="" )
}
# [1] "F9-P00-T00-HEADER-SAMPLE-10.CSV"
# [2] "F9-P01-T00-SUMMARY-SAMPLE-10.CSV"
# [3] "F9-P08-T00-REVENUE-SAMPLE-10.CSV"
# [4] "F9-P09-T00-EXPENSES-SAMPLE-10.CSV"
# [5] "F9-P10-T00-BALANCE-SHEET-SAMPLE-10.CSV"For our toy example, we are particularly interested in the part of the 990 form that collects information about the board members in a nonprofit. This is known as Part VII data. We will begin by pulling two years of this data.
There are two main ways to access the data. You can download the data locally to your computer from the data catalog and read it into your R environment or you can use pre-built functions to read it directly in R. We present examples of both below.
There are several data cleaning and preparation steps that need to be
performed on the base data before our analysis. These are beyond the
scope of this toy example. Instead, we provide a cleaned subset of the
data. We provide two dataframes of 1000 and 10 unique nonprofits that
experienced at least one CEO transition between 2009 and 2019. It should
be noted that it is possible for these organizations to experience more
than one transition during this time frame. As such, we have a total of
1067 transitions. We can analyze various facets of these
transitions.
ceo_trans_10 <- read.csv("toy_CEO_trans_10EIN.csv" )
ceo_trans_1000 <- read.csv("toy_CEO_trans_1000EIN.csv")The first thing we want to do with these data are to add financial
information about the orgs during each respective year. We can again
pull this info from the open data collective. This step takes about 10
minutes to run so we will comment out the lines and run similar steps
with the data of 10 nonprofits and pre-subset financial
dataframes.
summary <- read.csv("F9-P01-T00-SUMMARY-SAMPLE-10.csv")
revenue <- read.csv("F9-P08-T00-REVENUE-SAMPLE-10.csv")
expenses <- read.csv("F9-P09-T00-EXPENSES-SAMPLE-10.csv")
#let's join everything together:
ceo_trans_10_fncl <- ceo_trans_10 %>% left_join(summary, by = c("TAXYR" = "TAX_YEAR",
"EIN" = "ORG_EIN"))
ceo_trans_10_fncl <- ceo_trans_10_fncl %>% left_join(revenue, by = c("TAXYR" = "TAX_YEAR",
"EIN" = "ORG_EIN"))
ceo_trans_10_fncl <- ceo_trans_10_fncl %>% left_join(expenses, by = c("TAXYR" = "TAX_YEAR",
"EIN" = "ORG_EIN"))
write.csv(ceo_trans_10_fncl, "CEO_trans_10EIN_fncl.csv" )
# summary2009 <- get_table( "F9-P01-T00-SUMMARY", year=2009 )
# summary2010 <- get_table( "F9-P01-T00-SUMMARY", year=2010 )
# summary2011 <- get_table( "F9-P01-T00-SUMMARY", year=2011 )
# summary2012 <- get_table( "F9-P01-T00-SUMMARY", year=2012 )
# summary2013 <- get_table( "F9-P01-T00-SUMMARY", year=2013 )
# summary2014 <- get_table( "F9-P01-T00-SUMMARY", year=2014 )
# summary2015 <- get_table( "F9-P01-T00-SUMMARY", year=2015 )
# summary2016 <- get_table( "F9-P01-T00-SUMMARY", year=2016 )
# summary2017 <- get_table( "F9-P01-T00-SUMMARY", year=2017 )
# summary2018 <- get_table( "F9-P01-T00-SUMMARY", year=2018 )
# summary2019 <- get_table( "F9-P01-T00-SUMMARY", year=2019 )
#
# revenue2009 <- get_table( "F9-P08-T00-REVENUE", year=2009 )
# revenue2010 <- get_table( "F9-P08-T00-REVENUE", year=2010 )
# revenue2011 <- get_table( "F9-P08-T00-REVENUE", year=2011 )
# revenue2012 <- get_table( "F9-P08-T00-REVENUE", year=2012 )
# revenue2013 <- get_table( "F9-P08-T00-REVENUE", year=2013 )
# revenue2014 <- get_table( "F9-P08-T00-REVENUE", year=2014 )
# revenue2015 <- get_table( "F9-P08-T00-REVENUE", year=2015 )
# revenue2016 <- get_table( "F9-P08-T00-REVENUE", year=2016 )
# revenue2017 <- get_table( "F9-P08-T00-REVENUE", year=2017 )
# revenue2018 <- get_table( "F9-P08-T00-REVENUE", year=2018 )
# revenue2019 <- get_table( "F9-P08-T00-REVENUE", year=2019 )
#
#
# expenses2009 <- get_table( "F9-P09-T00-EXPENSES", year=2009 )
# expenses2010 <- get_table( "F9-P09-T00-EXPENSES", year=2010 )
# expenses2011 <- get_table( "F9-P09-T00-EXPENSES", year=2011 )
# expenses2012 <- get_table( "F9-P09-T00-EXPENSES", year=2012 )
# expenses2013 <- get_table( "F9-P09-T00-EXPENSES", year=2013 )
# expenses2014 <- get_table( "F9-P09-T00-EXPENSES", year=2014 )
# expenses2015 <- get_table( "F9-P09-T00-EXPENSES", year=2015 )
# expenses2016 <- get_table( "F9-P09-T00-EXPENSES", year=2016 )
# expenses2017 <- get_table( "F9-P09-T00-EXPENSES", year=2017 )
# expenses2018 <- get_table( "F9-P09-T00-EXPENSES", year=2018 )
# expenses2019 <- get_table( "F9-P09-T00-EXPENSES", year=2019 )
#
#
# #Let's put them all together
# summary <- rbind(as.data.frame(summary2009), as.data.frame(summary2010),as.data.frame(summary2011),
# as.data.frame(summary2012),as.data.frame(summary2013),as.data.frame(summary2014),
# as.data.frame(summary2015),as.data.frame(summary2016),as.data.frame(summary2017),
# as.data.frame(summary2018),as.data.frame(summary2019))
#
# revenue <- rbind(as.data.frame(revenue2009),as.data.frame(revenue2010),as.data.frame(revenue2011),
# as.data.frame(revenue2012),as.data.frame(revenue2013),as.data.frame(revenue2014),
# as.data.frame(revenue2015),as.data.frame(revenue2016),as.data.frame(revenue2017),
# as.data.frame(revenue2018),as.data.frame(revenue2019))
#
# expenses <- rbind(as.data.frame(expenses2009),as.data.frame(expenses2010),as.data.frame(expenses2011),
# as.data.frame(expenses2012),as.data.frame(expenses2013),as.data.frame(expenses2014),
# as.data.frame(expenses2015),as.data.frame(expenses2016),as.data.frame(expenses2017),
# as.data.frame(expenses2018),as.data.frame(expenses2019))
#
#
#
#
# #Orgs can return various versions of their tax forms each year
# #For now, we will keep only the first observation, true analysis will need to revisit this decision
# summary <- summary %>% group_by(ORG_EIN, TAX_YEAR) %>%
# filter(row_number()==1)
#
# revenue <- revenue %>% group_by(ORG_EIN, TAX_YEAR) %>%
# filter(row_number()==1)
#
# expenses <- expenses %>% group_by(ORG_EIN, TAX_YEAR) %>%
# filter(row_number()==1)
#
#
#
# #Saving environment space by removing the other dfs
# remove(summary2009, summary2010, summary2011, summary2012, summary2013, summary2014,
# summary2015, summary2016, summary2017, summary2018, summary2019 )
# remove(expenses2009, expenses2010, expenses2011, expenses2012, expenses2013, expenses2014,
# expenses2015, expenses2016, expenses2017, expenses2018, expenses2019 )
# remove(balance2009, balance2010, balance2011, balance2012, balance2013, balance2014,
# balance2015, balance2016, balance2017, balance2018, balance2019 )
# #Let's join everything together
#
#
# ceo_trans_1000_fncl <- ceo_trans_1000 %>% left_join(summary, by = c("TAXYR" = "TAX_YEAR",
# "EIN" = "ORG_EIN"))
#
# ceo_trans_1000_fncl <- ceo_trans_1000_fncl %>% left_join(expenses, by = c("TAXYR" = "TAX_YEAR",
# "EIN" = "ORG_EIN"))
#
# ceo_trans_1000_fncl <- ceo_trans_1000_fncl %>% left_join(revenue, by = c("TAXYR" = "TAX_YEAR",
# "EIN" = "ORG_EIN"))
#
#
# write.csv(ceo_trans_1000_fncl, "toy_CEO_trans_1000EIN_fncl.csv")Now we’re ready for some analyses! We are most interested in understanding transitions by their gendering. That is to say that we want to know if there are differences in observable firm dimensions for nonprofits that experience a CEO transition from male to male relative to the other permutations of male to female, female to male, and female to female. Let’s start by looking at org differences by these transition types. We will use the 1000 EIN observations for this analysis. We have provided this dataset with the financial data attached (this was accomplished by running the commented lines above). For simplicity, we will only consider a few firm dimensions such as employee size and total assets.
#This df
ceo_trans_1000_fncl <- read.csv("toy_CEO_trans_1000EIN_fncl.csv")
#First need to denote transitions
ceo_trans_1000_fncl <- ceo_trans_1000_fncl %>%
arrange(EIN, transition_no, TAXYR)%>% #for each org, transition, and year,
group_by(EIN, transition_no) %>%
mutate(period = row_number())%>% #let's denote which period the row is relative to (i.e., how many years before /after a transition)
ungroup()
#Let's make this variable a bit more readable
ceo_trans_1000_fncl$period <- paste0("T", ceo_trans_1000_fncl$period-3)
ceo_trans_1000_fncl$period <- factor(ceo_trans_1000_fncl$period, levels = c("T-2", "T-1", "T0" , "T1", "T2"))
#Need to get transition type too
#We want to know if the transition is M>M, M>F, F>M, or F>M
#We will first create a string of the CEO gender throughout the 5 year period
#The we will create a column that keeps the first and last gender (this is the gender transition)
ceo_trans_1000_fncl<- ceo_trans_1000_fncl %>%
group_by(EIN, transition_no)%>%
mutate(trans_tot = str_c(unlist(strsplit(paste(gender, collapse = ""), "")), collapse = "")
) %>%
mutate(
trans_type = str_sub(trans_tot, 1, 1) %>%
str_c(str_sub(trans_tot, nchar(trans_tot), nchar(trans_tot))) # Combine 1st and last letter
)
#We want this variable to be a factor
ceo_trans_1000_fncl$trans_type <- as.factor(ceo_trans_1000_fncl$trans_type)
#We're going to get firm attributes by transition type
#Let's consider the attributes at t-1
#We'll consider basic variables like number of employees, total assets, etc.
#Note that further data cleaning is necessary for final presentation due to NA values input into some of these columns. For now, we will just remove the nas and continue with our analysis
temp <- ceo_trans_1000_fncl %>% group_by(trans_type)%>%
filter(period == "T-1") %>%
summarize(count = n(),
num_emp_av =mean(F9_01_ACT_GVRN_EMPL_TOT, na.rm = T),
num_emp_sd = sd(F9_01_ACT_GVRN_EMPL_TOT, na.rm = T),
num_vol_av = mean(F9_01_ACT_GVRN_VOL_TOT, na.rm = T),
num_vol_sd = sd(F9_01_ACT_GVRN_VOL_TOT, na.rm = T),
total_exp_av = mean(F9_01_EXP_TOT_PY, na.rm = T),
total_exp_sd = sd(F9_01_EXP_TOT_PY, na.rm = T),
total_assets_av = mean(F9_01_NAFB_ASSET_TOT_BOY, na.rm = T),
total_assets_sd = sd(F9_01_NAFB_ASSET_TOT_BOY, na.rm = T))
temp
There are 349 transitions from male to male (MM) transitions, 253 male
to female transitions (MF), 160 female to male transitions (FM), and 305
female to female transitions (FF) in our dataset. FF orgs have the
smallest number of employees, on average while MF have the highest. It
should be noted that the standard deviation for number of employees is
pretty high, suggesting a wide range of nonprofits. MM nonprofits have
the highest total expeness on average and the highest total assets.
Again, the SDs are wide, suggesting large variation across
observations.
The Glass Cliff Phenomenon hypothesizes that women are chosen for
positions of power when these positions are more precarious. One way to
denote a precarious position is by the firm’s financial performance;
poor financial performance suggests more precariousness. We acknowledge
that financial performance is comprised of several different dimensions
and it is sometimes hard to arrive at clean conclusions about “poorly
performing nonprofits.” For demonstration purposes we will specifically
focus on the financial performance metric of post-depreciation
profitability margin (podpm). This is defined as an income measure that
determines a firm’s profitability after incorporating non-cash expenses.
Higher values of this metric are generally desirable because the
indicate that an org is not lost its revenue to expenses. We will use
the package fiscal from the Open Data Collective to
calculate our variable of interest. The default parameters of the
respective functions are already built for the 990 naming conventions so
usage is pretty straight forward!
#Make sure to run the code chunk about to ensure your df_long_fncl has all the necessary variables
#site: https://github.com/Nonprofit-Open-Data-Collective/fiscal/tree/main/R
ceo_trans_1000_fncl <- get_podpm(ceo_trans_1000_fncl)
#Let's plot these measures
plot_temp <- ceo_trans_1000_fncl %>%
group_by(trans_type, period)%>%
summarize(median_podpm =median(podpm, na.rm = T))
ggplot(data = plot_temp, aes(x = period, y = median_podpm, group = trans_type, color = trans_type)) +
geom_line(linewidth = 1.5)+
geom_text_repel(aes(label = round(median_podpm,3)), size = 5, nudge_x = -0.07, nudge_y = 0.001, segment.size = 0, segment.color = NA)+
theme_bw( ) +
# theme(text = element_text(size = 24))+
scale_x_discrete(labels = c("T-2", "T-1", "Transition", "T+1", "T+2"))+
labs(color = "Transition") +
xlab("Period")+
ylab("Median Post-Depreciation Profitability Margin") +
ggtitle("Median Post-Depreciation Profitability Margin by Transition Type") +
geom_vline(xintercept = 3, linetype = "dashed")
################################################
#Now doing density plots of these respective vars
################################################
#Let's compare the MM density to MF density in the t-1 period for the vats
ceo_trans_1000_fncl_MM <- ceo_trans_1000_fncl %>% filter(trans_type == "MM" &
period == "T-1")
ceo_trans_1000_fncl_MF <- ceo_trans_1000_fncl %>% filter(trans_type == "MF"&
period == "T-1")
ggplot() +
geom_density(data = ceo_trans_1000_fncl_MM, aes(x = podpm, fill = "lightblue"), alpha = 0.5) +
geom_density(data = ceo_trans_1000_fncl_MF, aes(x = podpm, fill = "pink"), alpha = 0.5) +
xlim(-1,1)+
theme_bw()+
# theme(text = element_text(size = 24))+
scale_fill_manual(name = "Transition", values = c('lightblue', 'pink'), labels = c("pink" = "MF" , "lightblue" = "MM") ) +
xlab("Post-Depreciation Profitability Margin")+
ylab("Density") +
ggtitle("Density ofPost-Depreciation Profitability Margin by Transition at t-1") The glass cliff phenomenon suggest that financial precarious NPs (seen by a significant drop in their financials between T-2 and T-1) would more likely hire a female to the CEO position. We start by looking at the PODPM variable over the periods. MM organizations start with the highest median profitability margin but also experience the steepest drop between T-2 and T-1. We see a similar slope for FM organizations although the starting point is the lowest in the entire group. MF transitions do not appear to be preceded by steep changes in profitability. If the glass cliff hypothesis were true, we would expect the observed trajectory of FM but would not expect the other firms to have similar metrics around their transitions. This graph calls for further analysis on other financial measures to determine whether FM firms display a transition during significantly more precarious times than their counterparts.
As a final understanding of the data, we look at the distribution of
PODPM at T-1. We consider the two most relevant groups of MM and MF. We
see that the distribution of financial variables tends to be extremely
similar to both types of transitions, again failing to provide strong
motivation for a glass cliff phenomenon.